package com.victor.core.util;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;

/**
 * excel导出工具
 *
 * @author JIE.ZHAO
 * @date 2017年5月8日 下午3:13:38
 */
public class ExcelExportUtils {
    /**
     * 带NEW适用2007新版格式xlsx，不带的是xls
     */
    private static Logger logger = Logger.getLogger(ExcelExportUtils.class
            .getName());

    /**
     * 填写表头
     *
     * @param headersInfo
     * @param workbook
     * @param sheet
     * @param rowIndex
     * @author liu.xiangfei 2016年9月29日 上午11:10:14
     */
    public static void outputHeaders(final float rowHeight, String[] headersInfo, HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, HSSFCellStyle style) {
        HSSFRow row = sheet.createRow(rowIndex);
        row.setHeightInPoints(rowHeight);
        for (int i = 0; i < headersInfo.length; i++) {
            HSSFCell nextCell = row.createCell(i);
            nextCell.setCellStyle(style);
            nextCell.setCellValue(headersInfo[i]);
            sheet.autoSizeColumn(i, true);// 列宽自适应
        }
    }

    /**
     * 填写表头 没有边框的
     *
     * @param headersInfo
     * @param workbook
     * @param sheet
     * @param rowIndex
     * @author liu.xiangfei 2016年9月29日 上午11:10:14
     */
    public static void outputNoHeaders(final float rowHeight, String[] headersInfo, HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, HSSFCellStyle style) {
        HSSFRow row = sheet.createRow(rowIndex);
        row.setHeightInPoints(rowHeight);
        for (int i = 0; i < headersInfo.length; i++) {
            HSSFCell nextCell = row.createCell(i);
            nextCell.setCellStyle(style);
            nextCell.setCellValue(headersInfo[i]);
            sheet.autoSizeColumn(i, true);// 列宽自适应
        }
    }

    /**
     * 载入List数据
     *
     * @param headersInfo
     * @param list
     * @param workbook
     * @param sheet
     * @param rowIndex
     * @author liu.xiangfei 2016年9月29日 上午11:10:52
     */
    public static void outputColumns(final float rowHeight, String[] headersInfo, Map<String, Object> map, HSSFWorkbook workbook,
                                     HSSFSheet sheet, int rowIndex, HSSFCellStyle style) {
        HSSFRow nextRow = sheet.createRow(rowIndex);
        nextRow.setHeightInPoints(rowHeight);
        for (int j = 0; j < headersInfo.length; j++) {
            HSSFCell nextCell = nextRow.createCell(j);
            nextCell.setCellStyle(style);
            Object value = map.get(headersInfo[j]);
            nextCell.setCellValue(value == null ? "" : value.toString());
            sheet.autoSizeColumn(j, true);// 列宽自适应
        }

    }

    /**
     * 载入dto类型数组数据
     *
     * @param headersInfo
     * @param objs
     * @param workbook
     * @param sheet
     * @param rowIndex
     * @author liu.xiangfei 2016年9月29日 上午11:13:07
     */
    public static void outputColumnsByDtos(String[] headersInfo, Object[] objs,
                                           HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex) {
        // 列名样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);

        for (int i = 0; i < objs.length; i++) {
            Object obj = objs[i];
            HSSFRow nextRow = sheet.createRow(rowIndex + i);
            for (int j = 0; j < headersInfo.length; j++) {
                HSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = getFiledValueByName(headersInfo[j], obj);
                nextCell.setCellValue(value == null ? "" : value.toString());
            }
        }
    }

    public static void outputHeadersMerge(int firstRow, int lastRow, int firstCol, int lastCol, HSSFSheet sheet) {
        while (firstCol < lastCol) {
            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, firstCol));
            firstCol++;
        }

    }

    /**
     * 动态合并表头单元格，headnum为合并单元格的起始和结束的rowindex和colindex
     *
     * @param headNum
     * @param sheet
     * @author liu.xiangfei 2016年9月29日 上午11:14:48
     */
    public static void outputHeadersMerge(int[] headNum, HSSFSheet sheet) {
        // 动态合并单元格
        Integer startrow = headNum[0];
        Integer overrow = headNum[1];
        Integer startcol = headNum[2];
        Integer overcol = headNum[3];
        sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                startcol, overcol));
    }

    /**
     * 动态合并一组单元格
     *
     * @param sheet
     * @author jie.Zhao
     * @date 2017年5月15日 下午1:20:36
     */
    public static void outputHeadersMerge(List<int[]> list, HSSFSheet sheet) {
        // 动态合并单元格
        for (int[] headNum : list) {
            Integer startrow = headNum[0];
            Integer overrow = headNum[1];
            Integer startcol = headNum[2];
            Integer overcol = headNum[3];
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
    }


    /**************************************************************************/
    /****************************带New 生成的是xlsx******************************/
    /*************************************************************************/
    public static void outputHeadersNew(String[] headersInfo,
                                        XSSFWorkbook workbook, XSSFSheet sheet, int rowIndex) {
        // 列名样式
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);

        XSSFRow row = sheet.createRow(rowIndex);
        for (int i = 0; i < headersInfo.length; i++) {
            XSSFCell nextCell = row.createCell(i);
            nextCell.setCellStyle(style);
            nextCell.setCellValue(headersInfo[i]);
            sheet.autoSizeColumn(i, true);// 列宽自适应
        }
    }

    public static void outputColumnsNew(String[] headersInfo,
                                        List<Map<String, Object>> list, XSSFWorkbook workbook,
                                        XSSFSheet sheet, int rowIndex) {
        // 列名样式
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);

        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            XSSFRow nextRow = sheet.createRow(rowIndex + i);
            for (int j = 0; j < headersInfo.length; j++) {
                XSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = map.get(headersInfo[j]);
                nextCell.setCellValue(value == null ? "" : value.toString());
            }
        }
    }

    public static void outputHeadersMergeNew(String[] headNum, XSSFSheet sheet) {
        // 动态合并单元格
        for (int i = 0; i < headNum.length; i++) {
            String[] temp = headNum[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
    }

    public static void outputColumnsByDtosNew(String[] headersInfo,
                                              Object[] objs, XSSFWorkbook workbook, XSSFSheet sheet, int rowIndex) {
        // 列名样式
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);

        for (int i = 0; i < objs.length; i++) {
            Object obj = objs[i];
            XSSFRow nextRow = sheet.createRow(rowIndex + i);
            for (int j = 0; j < headersInfo.length; j++) {
                XSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = getFiledValueByName(headersInfo[j], obj);
                nextCell.setCellValue(value == null ? "" : value.toString());
            }
        }
    }

    public static Object getFiledValueByName(String filedName, Object obj) {
        String firstLetter = filedName.substring(0, 1).toUpperCase();
        String getter = "get" + firstLetter + filedName.substring(1);
        try {
            Method method = obj.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(obj, new Object[]{});
            return value;
        } catch (Exception e) {
            logger.error("没有该属性" + filedName);
            return null;
        }
    }
}
